package edu.mcw.rgd.clinminer.umls.domain;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.log4j.Logger;
import org.eclipse.persistence.annotations.ReadOnly;
import org.eclipse.persistence.config.QueryHints;
import org.eclipse.persistence.exceptions.DatabaseException;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.jpa.activerecord.RooJpaActiveRecord;
import org.springframework.roo.addon.tostring.RooToString;

import edu.mcw.rgd.clinminer.data.ClinminerException;
import edu.mcw.rgd.clinminer.umls.type.AUI;
import edu.mcw.rgd.clinminer.umls.type.LocalCode;

@RooJavaBean
@RooToString
@RooJpaActiveRecord(persistenceUnit = "persistenceUnitUMLS", versionColumn = "")
@ReadOnly
// see
// http://www.ncbi.nlm.nih.gov/books/NBK9685/table/ch03.T.concept_names_and_sources_file__m/?report=objectonly
@NamedQueries({
		@NamedQuery(name = "findSynonyms", query = "SELECT DISTINCT LOWER(C.str) FROM MrConso C WHERE C.sab=:sab AND C.code=:code ORDER BY LOWER(C.str)"),
		// need to account for all the different labels in sources
		@NamedQuery(name = "findPreferredForCode", query = "SELECT o FROM MrConso o WHERE o.code=:code AND (o.tty='PT' OR o.tty='OP' OR o.tty='LPN' OR o.tty='MTH_LN' OR o.tty='HT' OR o.tty='LT' OR o.tty='HG' OR o.tty='SMQ' OR o.tty='MH' OR o.tty='NM' OR o.tty='PTAV' OR o.tty='PTCS') ORDER BY o.tty DESC"),
		@NamedQuery(name = "findCuisForCode", query = "SELECT DISTINCT o.cui FROM MrConso o WHERE o.code=:code AND o.suppress='N' AND (o.sab='RXNORM' OR (o.sab!='RXNORM' AND o.ispref='Y')) AND (o.sab='RXNORM' OR o.sab='SNOMEDCT' OR o.sab='LNC' OR o.sab='NDFRT')"),
		@NamedQuery(name = "findSuppressedCuisForCode", query = "SELECT DISTINCT o.cui FROM MrConso o WHERE o.code=:code AND o.suppress!='N' AND (o.sab='RXNORM' OR o.sab='SNOMEDCT' OR o.sab='LNC' OR o.sab='NDFRT')"),
		@NamedQuery(name = "findPreferredLabel", query = "SELECT o.str FROM MrConso o WHERE o.lat='ENG' AND o.ts='P' AND o.stt='PF' AND o.ispref='Y' AND o.cui=:cui"),
		@NamedQuery(name = "findSearchSuggestions", query = "SELECT DISTINCT o.str FROM MrConso o WHERE o.sab=:sab and UPPER(o.str) LIKE UPPER(:str) ORDER BY LENGTH(o.str) ASC"),
		@NamedQuery(name = "countSearchSuggestions", query = "SELECT COUNT(DISTINCT o.str) FROM MrConso o WHERE o.sab=:sab and UPPER(o.str) LIKE UPPER(:str)"), })
public class MrConso {
	private static final Logger log = Logger.getLogger(MrConso.class);

	@Id
	@Column(name = "AUI")
	private String aui;

	@Column(name = "SAB")
	private String sab;

	@Column(name = "CUI")
	private String cui;

	@Column(name = "CODE")
	private String code;

	@Column(name = "STR")
	private String str;

	@Column(name = "LAT")
	private String lat;

	@Column(name = "TS")
	private String ts;

	@Column(name = "STT")
	private String stt;

	@Column(name = "TTY")
	private String tty;

	@Column(name = "ISPREF")
	private String ispref;

	@Column(name = "SUPPRESS")
	private String suppress;

	public Collection<String> getSynonyms() {
		List<String> result = entityManager()
				.createNamedQuery("findSynonyms", String.class)
				.setParameter("sab", sab).setParameter("code", code)
				.getResultList();
		return result;
	}

	public static List<String> findCuisForCode(String code) {
		List<String> result = entityManager()
				.createNamedQuery("findCuisForCode", String.class)
				.setParameter("code", code).getResultList();
		return result;
	}

	public static List<String> findSuppressedCuisForCode(String code) {
		List<String> result = entityManager()
				.createNamedQuery("findSuppressedCuisForCode", String.class)
				.setParameter("code", code).getResultList();
		return result;
	}

	public static String findPrefLabelForCui(String cui) {
		List<String> result = entityManager()
				.createNamedQuery("findPreferredLabel", String.class)
				.setParameter("cui", cui).getResultList();
		// should not happen
		if (result.isEmpty() || result.size() > 1) {
			throw new UnsupportedOperationException("no label for cui " + cui);
		}
		return result.get(0);
	}

	private final static String oracleTextProgression = "CONTAINS(str, '<query>"
			+ "<textquery lang=\"ENGLISH\" grammar=\"CONTEXT\">'"
			+ "|| ? ||"
			+ "'<progression>"
			+ "<seq><rewrite>transform((TOKENS, \"{\", \"}\", \" \"))</rewrite></seq>"
			+ "<seq><rewrite>transform((TOKENS, \"{\", \"}\", \" ; \"))</rewrite></seq>"
			+ "<seq><rewrite>transform((TOKENS, \"{\", \"}\", \"AND\"))</rewrite></seq>"
			+ "<seq><rewrite>transform((TOKENS, \"%\", \"%\", \"AND\"))</rewrite></seq>"
			+ "<seq><rewrite>transform((TOKENS, \"%\", \"%\", \"ACCUM\"))</rewrite></seq>"
			+ "<seq><rewrite>transform((TOKENS, \"fuzzy(\", \")\", \"ACCUM\"))</rewrite></seq>"
			+ "</progression>"
			+ "</textquery>"
			+ "<score datatype=\"INTEGER\" algorithm=\"COUNT\"/>"
			+ "</query>', 1) > 0";

	public static Collection<String> findSearchSuggestionCodes(String keywords,
			String sab, Integer limit) throws ClinminerException {

		String s = "SELECT code FROM (SELECT code,"
				+ "RANK() OVER( PARTITION BY code ORDER BY SCORE(1) DESC, LENGTH(str) ASC ) rnk"
				+ " FROM MRCONSO WHERE "
				+ oracleTextProgression
				+ " AND sab=? AND suppress='N' ORDER BY SCORE(1) DESC, LENGTH(str) ASC"
				+ ") WHERE rnk=1";

		Query query = entityManager().createNativeQuery(s);
		query.setParameter(1, keywords);
		query.setParameter(2, sab);
		query.setMaxResults(limit);

		query.setHint(QueryHints.JDBC_TIMEOUT, 60);

		List<String> result;
		try {
			result = query.getResultList();
		} catch (DatabaseException e) {
			log.debug(keywords + " -> " + e.getInternalException().getMessage());
			return findSearchSuggestionCodesFailover(keywords, sab, limit);
		}

		return result;
	}

	public static Number countSearchSuggestions(String keywords, String sab) {

		String s = "SELECT COUNT(DISTINCT code)" + " FROM MRCONSO WHERE "
				+ oracleTextProgression + " AND sab=? AND suppress='N'";

		Query query = entityManager().createNativeQuery(s);

		query.setHint(QueryHints.JDBC_TIMEOUT, 1);
		query.setParameter(1, keywords);
		query.setParameter(2, sab);

		List<Long> result;
		try {
			result = query.getResultList();
		} catch (DatabaseException e) {
			log.debug("ERROR IN COUNTING RESULTS " + keywords + " - "
					+ e.getMessage());
			return null;
		}

		return result.get(0);
	}

	/**
	 * A JPA failover version in case the native query fails
	 *
	 * @param keywords
	 * @param sab
	 * @param limit
	 * @return
	 * @throws ClinminerException
	 */
	private static Collection<String> findSearchSuggestionCodesFailover(
			String keywords, String sab, int limit) throws ClinminerException {
		// entityManager().createNativeQuery(
		// "ALTER SESSION SET cursor_sharing='EXACT'").getFirstResult();
		CriteriaBuilder cb = entityManager().getCriteriaBuilder();

		CriteriaQuery<String> q = cb.createQuery(String.class);
		Root<MrConso> from = q.from(MrConso.class);

		List<Predicate> predicates = new ArrayList<Predicate>();

		Expression<String> str = from.get("str");
		Predicate predicate = cb.like(str, keywords);
		predicates.add(predicate);

		// add source filter
		predicates.add(cb.equal(from.get("sab"), sab));
		// add suppress/obsolete filter
		predicates.add(cb.equal(from.get("suppress"), "N"));
		// here's the actual select
		q.multiselect(from.get("code")).where(
				cb.and(predicates.toArray(new Predicate[0])));
		q.distinct(true);
		q.orderBy(cb.asc(cb.length(str)));

		TypedQuery<String> query = entityManager().createQuery(q);
		query.setHint(QueryHints.JDBC_TIMEOUT, 1);

		query.setMaxResults(limit);

		List<String> result;
		try {
			result = query.getResultList();
		} catch (DatabaseException e) {
			log.debug(keywords + " -> " + e.getInternalException().getMessage());
			throw new ClinminerException(e.getInternalException().getMessage());
		}

		return result;
	}

	/**
	 * This is a CriteriaQuery version of the native sql
	 * {@link #findSearchSuggestionCodes(String, String, Integer)} <b>It does
	 * not work due to a possible bug in Oracale on binding contains score
	 * label. </b> For more info on the problem see
	 * https://forums.oracle.com/forums/thread.jspa?threadID=91417
	 *
	 * @param keywords
	 * @param sab
	 * @param limit
	 * @return
	 * @throws ClinminerException
	 */
	@SuppressWarnings("unused")
	private Collection<String> findSearchSuggestionCodesEx(String keywords,
			String sab, int limit) throws ClinminerException {
		// entityManager().createNativeQuery(
		// "ALTER SESSION SET cursor_sharing='EXACT'").getFirstResult();

		CriteriaBuilder cb = entityManager().getCriteriaBuilder();

		CriteriaQuery<String> q = cb.createQuery(String.class);
		Root<MrConso> from = q.from(MrConso.class);

		// create str LIKE keyword AND for every keyword in the list
		List<Predicate> predicates = new ArrayList<Predicate>();

		Expression<String> path = from.get("str");
		Predicate predicate = cb.gt(
				cb.function("CONTAINS", Integer.class, path,
						cb.literal(keywords + "%"), cb.literal(1)), 0);
		predicates.add(predicate);

		// add source filter
		predicates.add(cb.equal(from.get("sab"), sab));
		// add suppress/obsolete filter
		predicates.add(cb.equal(from.get("suppress"), "N"));
		// here's the actual select
		q.multiselect(from.get("code")).where(
				cb.and(predicates.toArray(new Predicate[0])));
		// .distinct(true);
		// q.orderBy(cb.desc(cb.function("SCORE", Integer.class,
		// cb.literal(1))));

		TypedQuery<String> query = entityManager().createQuery(q);
		query.setHint(QueryHints.JDBC_TIMEOUT, 1);

		query.setMaxResults(limit);

		List<String> result;
		try {
			result = query.getResultList();
		} catch (DatabaseException e) {
			log.debug(keywords + " -> " + e.getInternalException().getMessage());
			throw new ClinminerException(e.getInternalException().getMessage());
		}

		return result;
	}

	public static MrConso findMrConso(AUI aui) {
		return findMrConso(aui.toString());
	}

	public static MrConso findMrConso(LocalCode code) {
		List<MrConso> result = entityManager()
				.createNamedQuery("findPreferredForCode", MrConso.class)
				.setParameter("code", code.toString()).setMaxResults(1)
				.getResultList();
		return (!result.isEmpty() ? result.get(0) : null);
	}
}
